package com.utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.StringUtils;

import com.utils.Constants;
import com.utils.UtilsLog;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

/**
 * Excel handler
 * @author zhangxf
 *
 */
public class UtilsExcel {
	
	public static Workbook readExcel(File file){
		InputStream fs = null;
		Workbook workBook = null;
		
		try {
			// 加载excel文件
			fs = new FileInputStream(file);
			// 得到 workbook
			workBook = Workbook.getWorkbook(fs);
		} catch (FileNotFoundException e) {
			UtilsLog.LOG.error(file.getPath() + "/"+file.getName() + " 未找到要读取的文件！ ");
		} catch (BiffException e) {
			UtilsLog.LOG.error(file.getPath() + "/"+file.getName() + " 读取文件时异常,不支持excel2007,并且文件后缀必须为xls,请另存为xls格式!");
		} catch (IOException e) {
			UtilsLog.LOG.error(file.getPath() + "/"+file.getName() + " 读取文件时异常: " + e.getMessage());
		}finally{
			if(fs != null){
				try {
					fs.close();
				} catch (IOException e) {
					UtilsLog.LOG.error(file.getPath() + "/"+file.getName() + " 关闭文件流时异常: " + e.getMessage());
				}
			}
		}
		
		return workBook;
	}
	public static Workbook readExcel(String filePath){
		InputStream fs = null;
		Workbook workBook = null;

		try {
			// 加载excel文件
			fs = new FileInputStream(filePath);
			// 得到 workbook
			workBook = Workbook.getWorkbook(fs);
		} catch (FileNotFoundException e) {
			UtilsLog.LOG.error(filePath + " 未找到要读取的文件！ ");
		} catch (BiffException e) {
			UtilsLog.LOG.error(filePath + " 读取文件时异常,不支持excel2007,并且文件后缀必须为xls,请另存为xls格式!");
		} catch (IOException e) {
			UtilsLog.LOG.error(filePath + " 读取文件时异常: " + e.getMessage());
		}finally{
			if(fs != null){
				try {
					fs.close();
				} catch (IOException e) {
					UtilsLog.LOG.error(filePath + " 关闭文件流时异常: " + e.getMessage());
				}
			}
		}
		
		return workBook;
	}
	
	/**
	 * 读取excel,每换行则说明是一个block 
	 * 每两个单元格之间用%%%号分割
	 * 每两行之间用###号分割
	 * @param sheet
	 * @return block List
	 */
	public static List<String> readSheet(Sheet sheet){
		List<String> blockList = new ArrayList<String>();
		StringBuffer oneBlock = new StringBuffer();
		StringBuffer rowContentSB = new StringBuffer();
		String rowCellValue;
		boolean isBlockOver = false;
		for (int i = 0; i < sheet.getRows(); i++) {
			/** 拼装一行数据 */ 
			for (Cell cell2 : sheet.getRow(i)) { 
				rowCellValue = cell2.getContents().trim();
				rowContentSB.append(rowCellValue + Constants.SPLIT_SIGN_CELL);
			}
			
			/** length < 1说明读取到了一行空格 */
			int length = rowContentSB.toString().replaceAll(Constants.SPLIT_SIGN_CELL, "").length();
			if (length < 1) {
				if (oneBlock.toString().trim().length() > 1) {
					isBlockOver = true;
				}
			} else {
				oneBlock.append(rowContentSB.toString() + Constants.SPLIT_SIGN);
			}

			if (i == sheet.getRows() - 1) {
				isBlockOver = true;
			}
			
			/**一个block是否结束*/
			if(isBlockOver){
				blockList.add(oneBlock.toString());
				oneBlock.delete(0, oneBlock.length());
				isBlockOver = false;
			}
			
			/**删除上一行数据*/
			rowContentSB.delete(0, rowContentSB.length());
		}
		
		return blockList;
	}
	
	/**
	 * 智能哪一行判断列头
	 * @param dataArray
	 * @return
	 */
	public static int judgeColumnNum(String[] dataArray) {
		int columns_nums = 0;
		String[] rowDataArray = null;
		
		int row_max_length = 0;
		for (int i = 0; i < dataArray.length; i++) {
			rowDataArray = dataArray[i].split(Constants.SPLIT_SIGN_CELL);
			int temp_var = 0;
			//不为空的单元格计数
			for (int j = 0; j < rowDataArray.length; j++) {
				if(!StringUtils.isBlank(rowDataArray[j])){
					temp_var = temp_var + 1;
				}
			}
			if(temp_var > row_max_length){
				row_max_length = temp_var;
				columns_nums = i; 
			}
		}
		
		return columns_nums;
	}
}



